# -*- coding: utf-8 -*-
from datetime import datetime, timedelta
from typing import Optional
import uuid

from sqlalchemy import func, text
from sqlalchemy.orm.attributes import flag_modified

from db.session import with_session
from db.models.user import Group, UserGroup, GroupPermission
from db.models.knowledge import KnowledgeBase
from db.models.clause import ClauseFile, ClauseGroups
from db.models.user import User


@with_session
def db_check_group_name_exists(session, name: str):
    """检查组名是否已存在"""
    return session.query(Group).filter(Group.name == name, Group.is_delete.is_(False)).first()


@with_session
def db_create_group(session, name: str, description: str = None, owner_id: int = None,
                    expiration_time: datetime = None):
    """创建新组"""
    group = Group(
        name=name,
        description=description,
        owner=owner_id,
        expiration_time=expiration_time
    )
    session.add(group)
    session.commit()
    session.refresh(group)
    return group


@with_session
def db_add_user_to_group(session, user_id: int, group_id: int):
    """将用户添加到组"""
    user_group = UserGroup(user=user_id, group=group_id)
    session.add(user_group)
    session.commit()
    return user_group


@with_session
def db_remove_all_users_from_group(session, group_id: int):
    """从组中移除所有用户"""
    session.query(UserGroup).filter(UserGroup.group == group_id).delete()
    session.commit()


@with_session
def db_soft_delete_group(session, group_id: int):
    """软删除组"""
    group = session.query(Group).get(group_id)
    if group:
        group.is_delete = True
        session.commit()


@with_session
def db_check_user_group_permission(session, user_id: int, group_id: int):
    """检查用户是否有组权限"""
    return session.query(UserGroup).filter(
        UserGroup.user == user_id,
        UserGroup.group == group_id
    ).first() is not None


@with_session
def db_get_group_users(session, group_id: int, username: str = None):
    """获取组内用户"""
    query = session.query(User).join(UserGroup, UserGroup.user == User.id).filter(
        UserGroup.group == group_id,
        User.is_delete.is_(False)
    )
    if username:
        query = query.filter(User.nickname.startswith(username))
    return query.all()


@with_session
def db_update_group_info(session, group_id: int, name: str = None, description: str = None,
                         expiration_time: datetime = None):
    """更新组信息"""
    group = session.query(Group).get(group_id)
    if group:
        if name is not None:
            group.name = name
        if description is not None:
            group.description = description
        if expiration_time is not None:
            group.expiration_time = expiration_time
        session.commit()


@with_session
def db_remove_user_from_group(session, user_id: int, group_id: int):
    """从组中移除用户"""
    session.query(UserGroup).filter(
        UserGroup.user == user_id,
        UserGroup.group == group_id
    ).delete()
    session.commit()


@with_session
def db_get_group_permissions(session, group_id: int):
    """获取组权限"""
    permissions = session.query(GroupPermission).filter(
        GroupPermission.group == group_id
    ).all()
    return [{"id": p.id, "permission": p.permission} for p in permissions]


@with_session
def db_set_group_expiration(session, group_id: int, expiration_time: datetime):
    """设置组过期时间"""
    group = session.query(Group).get(group_id)
    if group:
        group.expiration_time = expiration_time
        session.commit()


# ==================== 用户组管理相关函数 ====================

@with_session
def db_add_user_to_group(session, user_id: int, group_id: int):
    """将用户添加到组"""
    # 检查用户是否已在组中
    existing = session.query(UserGroup).filter(
        UserGroup.user == user_id,
        UserGroup.group == group_id
    ).first()

    if existing:
        return existing

    user_group = UserGroup(user=user_id, group=group_id)
    session.add(user_group)
    session.commit()
    session.refresh(user_group)
    return user_group


@with_session
def db_remove_user_from_group(session, user_id: int, group_id: int):
    """从组中移除用户"""
    result = session.query(UserGroup).filter(
        UserGroup.user == user_id,
        UserGroup.group == group_id
    ).delete()
    session.commit()
    return result > 0


@with_session
def db_get_group_members(session, group_id: int, username_filter: str = None):
    """获取组内所有成员"""
    query = session.query(User).join(UserGroup, UserGroup.user == User.id).filter(
        UserGroup.group == group_id,
        User.is_delete.is_(False)
    )

    if username_filter:
        query = query.filter(User.nickname.startswith(username_filter))

    return query.all()


@with_session
def db_check_user_in_group(session, user_id: int, group_id: int):
    """检查用户是否在组中"""
    return session.query(UserGroup).filter(
        UserGroup.user == user_id,
        UserGroup.group == group_id
    ).first() is not None


@with_session
def db_get_user_groups(session, user_id: int):
    """获取用户所属的所有组"""
    return session.query(Group).join(UserGroup, Group.id == UserGroup.group).filter(
        UserGroup.user == user_id,
        Group.is_delete.is_(False)
    ).all()


@with_session
def db_get_group_by_name(session, name: str):
    """根据组名获取组"""
    return session.query(Group).filter(
        Group.name == name,
        Group.is_delete.is_(False)
    ).first()


@with_session
def db_update_group(session, group_id: int, name: str = None, description: str = None,
                    expiration_time: datetime = None):
    """更新组信息"""
    group = session.query(Group).get(group_id)
    if not group:
        return None

    if name is not None:
        group.name = name
    if description is not None:
        group.description = description
    if expiration_time is not None:
        group.expiration_time = expiration_time

    session.commit()
    return group


@with_session
def db_delete_group(session, group_id: int):
    """软删除组"""
    group = session.query(Group).get(group_id)
    if not group:
        return False

    group.is_delete = True
    session.commit()
    return True


@with_session
def db_remove_all_users_from_group(session, group_id: int):
    """从组中移除所有用户"""
    result = session.query(UserGroup).filter(UserGroup.group == group_id).delete()
    session.commit()
    return result


@with_session
def db_get_group_permissions(session, group_id: int):
    """获取组权限列表"""
    permissions = session.query(GroupPermission).filter(
        GroupPermission.group == group_id
    ).all()
    return [{"id": p.id, "permission": p.permission} for p in permissions]


@with_session
def db_add_permission_to_group(session, group_id: int, permission: str):
    """为组添加权限"""
    group_permission = GroupPermission(group=group_id, permission=permission)
    session.add(group_permission)
    session.commit()
    session.refresh(group_permission)
    return group_permission


@with_session
def db_remove_permission_from_group(session, group_id: int, permission: str):
    """从组中移除权限"""
    result = session.query(GroupPermission).filter(
        GroupPermission.group == group_id,
        GroupPermission.permission == permission
    ).delete()
    session.commit()
    return result > 0


@with_session
def db_check_group_permission(session, group_id: int, permission: str):
    """检查组是否有特定权限"""
    return session.query(GroupPermission).filter(
        GroupPermission.group == group_id,
        GroupPermission.permission == permission
    ).first() is not None


@with_session
def db_get_groups_by_owner(session, owner_id: int):
    """获取用户创建的所有组"""
    return session.query(Group).filter(
        Group.owner == owner_id,
        Group.is_delete.is_(False)
    ).all()


@with_session
def db_get_expired_groups(session):
    """获取已过期的组"""
    return session.query(Group).filter(
        Group.expiration_time < datetime.now(),
        Group.is_delete.is_(False)
    ).all()


@with_session
def db_get_groups_expiring_soon(session, days: int = 7):
    """获取即将过期的组"""
    expiration_threshold = datetime.now() + timedelta(days=days)
    return session.query(Group).filter(
        Group.expiration_time <= expiration_threshold,
        Group.expiration_time > datetime.now(),
        Group.is_delete.is_(False)
    ).all()


# ==================== 权限管理相关函数 ====================

@with_session
def db_add_permission_to_group(session, group_id: int, permission: str):
    """为组添加权限"""
    # 检查权限是否已存在
    existing = session.query(GroupPermission).filter(
        GroupPermission.group == group_id,
        GroupPermission.permission == permission
    ).first()

    if existing:
        return existing

    group_permission = GroupPermission(group=group_id, permission=permission)
    session.add(group_permission)
    session.commit()
    session.refresh(group_permission)
    return group_permission


@with_session
def db_remove_permission_from_group(session, group_id: int, permission: str):
    """从组中移除权限"""
    result = session.query(GroupPermission).filter(
        GroupPermission.group == group_id,
        GroupPermission.permission == permission
    ).delete()
    session.commit()
    return result > 0


@with_session
def db_check_permission_exists(session, group_id: int, permission: str):
    """检查权限是否存在"""
    return session.query(GroupPermission).filter(
        GroupPermission.group == group_id,
        GroupPermission.permission == permission
    ).first() is not None


@with_session
def db_get_group_by_id_for_permission(session, group_id: int):
    """根据ID获取组（用于权限管理）"""
    return session.query(Group).filter(
        Group.id == group_id,
        Group.is_delete.is_(False)
    ).first()


# ==================== 用户组资源绑定相关函数 ====================

@with_session
def db_add_group_resource(session, group_id: int, kb_id_list: list, clause_id_list: list):
    """
    为用户组绑定知识库和条款库资源

    Args:
        session: 数据库会话
        group_id: 用户组ID
        kb_id_list: 知识库ID列表
        clause_id_list: 条款库ID列表

    Returns:
        bool: 操作是否成功
    """
    try:

        # 处理知识库绑定
        if kb_id_list:

            # 查询知识库
            knowledge_bases = session.query(KnowledgeBase).filter(
                KnowledgeBase.is_delete.is_(False),
                KnowledgeBase.id.in_(kb_id_list)
            ).all()

            # 检查是否存在无效的知识库ID
            kb_existing_ids = {kb.id for kb in knowledge_bases}
            non_kb_existing_ids = set(kb_id_list) - kb_existing_ids
            if non_kb_existing_ids:
                raise ValueError(f"含有不存在的知识库: {non_kb_existing_ids}")

            # 查询所有 group_ids 中包含目标 group_id 的记录
            # 使用JSON_CONTAINS函数查询
            has_group_id_knowledge_bases = session.query(KnowledgeBase).filter(
                KnowledgeBase.is_delete.is_(False),
                func.json_contains(KnowledgeBase.group_ids, str(group_id))
            ).all()

            # 遍历每条记录，更新 group_ids
            for kb in has_group_id_knowledge_bases:
                # 获取实际的group_ids列表
                actual_group_ids = kb.group_ids if isinstance(kb.group_ids, list) else []
                if group_id in actual_group_ids:
                    actual_group_ids.remove(group_id)
                    # 更新数据库中的group_ids
                    kb.group_ids = actual_group_ids
                    flag_modified(kb, "group_ids")
                    session.add(kb)

            # 绑定知识库到用户组
            for kb in knowledge_bases:
                # 获取当前的group_ids
                current_group_ids = kb.group_ids if isinstance(kb.group_ids, list) else []
                if group_id not in current_group_ids:
                    current_group_ids.append(group_id)
                    # 更新数据库中的group_ids
                    kb.group_ids = current_group_ids
                    flag_modified(kb, "group_ids")
                    session.add(kb)

        # 处理条款库绑定
        if clause_id_list:
            # 查询条款库
            clauses = session.query(ClauseFile).filter(
                ClauseFile.is_delete.is_(False),
                ClauseFile.id.in_(clause_id_list)
            ).all()

            # 检查是否存在无效的条款库ID
            clause_existing_ids = {clause.id for clause in clauses}
            non_clause_existing_ids = set(clause_id_list) - clause_existing_ids
            if non_clause_existing_ids:
                raise ValueError(f"含有不存在的条款库: {non_clause_existing_ids}")

            # 删除旧的条款库绑定
            session.query(ClauseGroups).filter_by(group=group_id).delete()

            # 绑定条款库到用户组
            for clause in clauses:
                if not session.query(ClauseGroups).filter_by(group=group_id, clause=clause.id).first():
                    session.add(ClauseGroups(group=group_id, clause=clause.id))

        session.commit()
        return True

    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_get_group_knowledge_bases(session, group_id: int):
    """获取用户组绑定的知识库列表"""

    # 使用JSON_CONTAINS函数查询包含指定group_id的知识库
    knowledge_bases = session.query(KnowledgeBase).filter(
        KnowledgeBase.is_delete.is_(False),
        func.json_contains(KnowledgeBase.group_ids, str(group_id))
    ).all()

    return knowledge_bases


@with_session
def db_get_group_clauses(session, group_id: int):
    """获取用户组绑定的条款库列表"""
    return session.query(ClauseFile).join(
        ClauseGroups, ClauseFile.id == ClauseGroups.clause
    ).filter(
        ClauseGroups.group == group_id,
        ClauseFile.is_delete.is_(False)
    ).all()


@with_session
def db_remove_group_resource(session, group_id: int, kb_id_list: list = None, clause_id_list: list = None):
    """
    移除用户组的资源绑定

    Args:
        session: 数据库会话
        group_id: 用户组ID
        kb_id_list: 要移除的知识库ID列表（可选）
        clause_id_list: 要移除的条款库ID列表（可选）

    Returns:
        bool: 操作是否成功
    """
    try:

        # 移除知识库绑定
        if kb_id_list:
            knowledge_bases = session.query(KnowledgeBase).filter(
                KnowledgeBase.is_delete.is_(False),
                KnowledgeBase.id.in_(kb_id_list)
            ).all()

            for kb in knowledge_bases:
                if group_id in kb.group_ids:
                    kb.group_ids.remove(group_id)
                    flag_modified(kb, "group_ids")
                    session.add(kb)

        # 移除条款库绑定
        if clause_id_list:
            session.query(ClauseGroups).filter(
                ClauseGroups.group == group_id,
                ClauseGroups.clause.in_(clause_id_list)
            ).delete()

        session.commit()
        return True

    except Exception as e:
        session.rollback()
        raise e
